07. Exercise: Extract Text

Exercise: Extract Text

In the exercise below, you'll combine what you've learned about the FIND and MID functions to extract the first word found after the first occurrences of the word "data" in a series of sentences. For example, in the following sentence, your target word is "is":

Some programming languages use 0 based indexing. However, Excel uses 1 based index. This means that counting begins at the value of 1. Therefore, to find the index for the word data, we would count as shown in the image below.

Then for the extraction for this quiz, since this extraction is in the middle of a text string, you will use the function MID with the following syntax:

MID(text, start_num, num_chars)
text     
         text string containing the characters you want to extract
start_num    
         position of the first character you want to extract in text
num_chars
         the number of characters you want MID to return from text

Think about how this can be solved. Before you can use the MID function, you first need to FIND the word after data and the number of characters that are in it. To figure out the number of characters in the target word, you will need to FIND the space after it as well. The size of the target word will be equal to the position of the space after it minus the position of the start of the target word.

The exercise will step you through intermediate formulas to get to the final answer. As you figure out the correct formula in the top cell in each column, you will be able to "fill" (or copy) the cells down the column for all the cases you are given.

As a reminder, here is the syntax for using the FIND function:

FIND(find_text, within_text, [start_num])
find_text    
         the text you want to find
within_text   
         the text containing the text you want to find
start_num    
         OPTIONAL - character position at which to start the search

Task Description:

The following list has a series of steps for this exercise. As you complete each step, check it off the list. The quizzes in the task list can be found below.

Task List:

Task Feedback:

Congratulations! This was a tough one, but you did it!!!

First "data" location

QUESTION:

What is the first "data" location for the sentence in row 6?

SOLUTION:

NOTE: The solutions are expressed in RegEx pattern. Udacity uses these patterns to check the given answer

Target word location

QUESTION:

What is the location of the first letter of the target word in row 5?

SOLUTION:

NOTE: The solutions are expressed in RegEx pattern. Udacity uses these patterns to check the given answer

Target word length

QUESTION:

How long is the target word in row 3?

SOLUTION:

NOTE: The solutions are expressed in RegEx pattern. Udacity uses these patterns to check the given answer

Target words

QUESTION:

List the nine target words found separated by spaces (no commas or other punctuation please!)

SOLUTION:

NOTE: The solutions are expressed in RegEx pattern. Udacity uses these patterns to check the given answer

Nested formula

Which of the following formulas for G2 will calculate the target word and is only dependent upon the original sentence?
SOLUTION: =MID(A2,FIND(" ",A2,FIND("data",A2))+1,FIND(" ",A2,(FIND(" ",A2,FIND("data",A2))+1))-(FIND(" ",A2,FIND("data",A2))+1))

Next Concept